/* 
* Copyright (c) 2009, The University of Texas at Austin
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without modification, 
* are permitted provided that the following conditions are met:
*
* 1. Redistributions of source code must retain the above copyright notice, 
* this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright notice, 
* this list of conditions and the following disclaimer in the documentation and/or other materials 
* provided with the distribution.
*
* Neither the name of The University of Texas at Austin nor the names of its contributors may be 
* used to endorse or promote products derived from this software without specific prior written 
* permission.
* 
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
* IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND 
* FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS 
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
* PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF 
* THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
TRUNCATE TABLE TaxonomyNamesOrdered;
GO
WITH TaxonomyNamesOrdered_CTE (TaxID, ScientificName, Lvl, Lineage)
AS
(
	SELECT T.TaxID, TN.ScientificName, 0 As Lvl, CAST(CAST(TN.ScientificName AS VARCHAR(8000)) + ' \ ' AS VARCHAR(8000)) AS Lineage
	FROM Taxonomy AS T
	INNER JOIN
	TaxonomyNames AS TN
	ON T.TaxID=TN.TaxID
	WHERE T.TaxID = 1
	UNION ALL
	SELECT T.TaxID, TN.ScientificName, TCTE.Lvl + 1, CAST(TCTE.Lineage + CAST(TN.ScientificName AS VARCHAR(8000)) + ' \ ' AS VARCHAR(8000))
	FROM TaxonomyNamesOrdered_CTE AS TCTE
	INNER JOIN Taxonomy AS T
	ON TCTE.TaxID=T.ParentTaxID	
	INNER JOIN TaxonomyNames AS TN
	ON T.TaxID=TN.TaxID
)
INSERT INTO TaxonomyNamesOrdered(TaxID, ScientificName, LineageName, Level, SortOrder)
SELECT TaxID, ScientificName, Lineage, Lvl, ROW_NUMBER() OVER(ORDER BY Lineage) AS SortOrder
FROM TaxonomyNamesOrdered_CTE
ORDER BY SortOrder;